package org.yoao.moneymachine.dao;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.yoao.crm.DBManager.DBManager;
import org.yoao.moneymachine.bean.Order;

/**
 * @author Weishion.Yi
 * @description
 * @date 2010-10-4 下午03:12:13
 */
public class OrderDAO extends DBManager {
	private static final Logger log = Logger.getLogger(OrderDAO.class);

	public String findMaxOrderNumber() throws Exception {
		String sql = "select MAX(orderNumber) as orderNumber from mmorder";
		return querySingleValue(sql);
	}

	public int getDataCount(Map map) throws Exception {
		String sql = "select count(id) as orderCount from mmorder";
		String where = " where ";
		if (map != null && !map.isEmpty()) {
			Iterator iter = map.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next().toString();
				String value = map.get(key).toString();
				where = where + key + "='" + value + "' and ";
			}
		}
		if (where.endsWith(" and "))
			where = where.substring(0, where.length() - 4);
		if (where.equals(" where "))
			where = "";
		sql += where;
		String result = querySingleValue(sql);
		if (result == null || result.trim().equals(""))
			result = "0";
		return Integer.parseInt(result);
	}

	public List find(int startRow, int endRow, Map map) throws Exception {
		String sql = "select top "
				+ endRow
				+ " mmorder.id, product.proName as productName, mmorder.orderNumber, mmorder.subscriber, "
				+ "mmorder.mobile, mmorder.address, mmuser.userName as referral, mmorder.amount, mmorder.status, "
				+ "mmorder.createTime, baseuser.nickName as userSno, mmorder.isPay, mmorder.payNo, "
				+ "mmorder.payAmount, mmorder.memo from mmorder "
				+ "left join product on product.id=mmorder.productName "
				+ "left join mmuser on mmuser.expandCode=mmorder.referral "
				+ "left join baseuser on baseuser.userName=mmorder.userSno "
				+ "where mmorder.id not in(";
		String filterSql = "select top " + startRow
				+ " o2.id from mmorder as o2 ";

		String where = "";
		String fwhere = "";
		if (map != null && !map.isEmpty()) {
			Iterator iter = map.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next().toString();
				String value = map.get(key).toString();
				where = where + "mmorder." + key + "='" + value + "' and ";
				fwhere = fwhere + "o2." + key + "='" + value + "' and ";
			}
		}
		if (where.endsWith(" and "))
			where = where.substring(0, where.length() - 4);
		if (where.equals(""))
			where = null;
		if (fwhere.endsWith(" and "))
			fwhere = fwhere.substring(0, fwhere.length() - 4);
		if (fwhere.equals(""))
			fwhere = null;

		if (where == null) {
			fwhere = " order by o2.id desc";
			sql = sql + filterSql + fwhere + ") order by mmorder.id desc";
		} else {
			filterSql = filterSql + " where " + fwhere + " order by o2.id desc";
			sql = sql + filterSql + ") and " + where
					+ " order by mmorder.id desc";
		}
		return find(sql);
	}

	public List findAll() throws Exception {
		String sql = "select id, orderNumber, productName, subscriber, mobile, address, referral, amount, status, createTime, userSno from mmorder";
		return find(sql);
	}

	public Order findById(String id) throws Exception {
		String sql = "select id, orderNumber, productName, subscriber, mobile, address, referral, amount, status, createTime, userSno from mmorder where id="
				+ id;
		return findSingle(querySingleRow(sql));
	}

	public Map findPayById(String id) throws Exception {
		String sql = "select mmorder.referral as expandCode, mmorder.amount, product.price from mmorder "
				+ "left join product on product.id = mmorder.productName "
				+ "where mmorder.id=" + id;
		return querySingleRow(sql);
	}

	private List find(String sql) throws Exception {
		List list = queryMultiColumn(sql);
		List result = null;
		if (list != null && !list.isEmpty()) {
			result = new ArrayList();
			for (int i = 0; i < list.size(); i++) {
				Map map = (Map) list.get(i);
				Order order = findSingle(map);
				if (order == null)
					order = new Order();
				result.add(order);
			}
		}
		return result;
	}

	private Order findSingle(Map data) throws Exception {
		Order order = null;
		if (data != null && !data.isEmpty()) {
			order = new Order();
			order.setId(data.get("ID").toString());
			order.setOrderNumber((String) data.get("ORDERNUMBER"));
			order.setProductName((String) data.get("PRODUCTNAME"));
			order.setSubscriber((String) data.get("SUBSCRIBER"));
			order.setMobile((String) data.get("MOBILE"));
			order.setAddress((String) data.get("ADDRESS"));
			order.setReferral((String) data.get("REFERRAL"));
			order.setAmount((String) data.get("AMOUNT"));
			order.setStatus((String) data.get("STATUS"));
			order.setCreateTime((String) data.get("CREATETIME"));
			order.setUserName((String) data.get("USERSNO"));
			order.setIsPay((String) data.get("ISPAY"));
			order.setPayNo((String) data.get("PAYNO"));
			order.setPayAmount((String) data.get("PAYAMOUNT"));
			order.setMemo((String) data.get("MEMO"));
		}
		return order;
	}

	public int insert(Order order) throws Exception {
		String sql = "insert into mmorder(orderNumber, productName, subscriber, mobile, address, referral, amount, status, createTime)values('"
				+ order.getOrderNumber()
				+ "', '"
				+ order.getProductName()
				+ "', '"
				+ order.getSubscriber()
				+ "', '"
				+ order.getMobile()
				+ "', '"
				+ order.getAddress()
				+ "', '"
				+ order.getReferral()
				+ "', '"
				+ order.getAmount()
				+ "', '"
				+ order.getStatus()
				+ "', '" + order.getCreateTime() + "')";
		return update(sql);
	}

	public int modifyStatus(String id, String value) throws Exception {
		String sql = null;
		if (id.indexOf(",") > 0)
			sql = "update mmorder set status='" + value + "' where id in(" + id
					+ ")";
		else
			sql = "update mmorder set status='" + value + "' where id=" + id;
		return update(sql);
	}

	public int modifyMemo(Order order) throws Exception {
		String sql = "update mmorder set memo='" + order.getMemo()
				+ "' where id=" + order.getId();
		return update(sql);
	}

	public int delete(String id) throws Exception {
		String sql = null;
		if (id.indexOf(",") > 0)
			sql = "delete from mmorder where id in(" + id + ")";
		else
			sql = "delete from mmorder where id=" + id;
		return update(sql);
	}

	//
	// public float findAllAmount(String currExpandCode) throws Exception{
	// String sql = "select expandCode from mmuser ";
	// if(currExpandCode != null && !currExpandCode.trim().equals("")){
	// sql = sql + " where userCode='"+currExpandCode+"'";
	// }
	// //获取当前用户的支付金额
	// float amountTotal = findAmountByExpandCode(currExpandCode);
	//
	// List list = querySingleColumn(sql);
	// if(list != null && !list.isEmpty()){
	// int size = list.size();
	// for(int i=0;i<size;i++){
	// String expandCode = (String)list.get(i);
	// float temp = findAllAmount(expandCode);
	// amountTotal = amountTotal + temp;
	// }
	// return amountTotal;
	// }else return amountTotal;
	// }

	public void modifyUserAmountTotal(String currExpandCode, int layer,
			float amount, float price, String type) throws Exception {
		double amountTotal = 0;
		if (layer == 1) {
			amountTotal = amount;
		} else if (layer == 2) {
			amountTotal = price * 0.05;
		} else if (layer == 3) {
			amountTotal = price * 0.025;
		} else if (layer == 4) {
			amountTotal = price * 0.015;
		} else {
			return;
		}
		DecimalFormat moneyFormat = new DecimalFormat("#.00");
		String userSql = null;
		if ("1".equals(type)) // 完成
			userSql = "update mmuser set amountTotal = amountTotal + "
					+ moneyFormat.format(amountTotal) + " where expandCode='"
					+ currExpandCode + "'";
		else if ("2".equals(type)) // 撤销
			userSql = "update mmuser set amountTotal = amountTotal - "
					+ moneyFormat.format(amountTotal) + " where expandCode='"
					+ currExpandCode + "'";
		update(userSql);

		String sql = "select userCode from mmuser where expandCode='"
				+ currExpandCode + "'";
		String userCode = querySingleValue(sql);
		if (userCode != null && !userCode.trim().equals("")) {
			modifyUserAmountTotal(userCode, layer + 1, amount, price, type);
		}
	}

	private float findAmountByExpandCode(String expandCode) throws Exception {
		String sql = "select sum(convert(float, amount)) as amount from mmorder where referral='"
				+ expandCode + "' and status='1'";
		String temp = querySingleValue(sql);
		if (temp == null || temp.trim().equals(""))
			temp = "0";
		float amount = 0;
		try {
			amount = Float.parseFloat(temp);
		} catch (Exception e) {
			log.error("支付金额[" + temp + "]转换出错");
		}
		System.out.println("amount:" + amount);
		return amount;
	}
}